USE <database_name>;
GO

SET NOCOUNT ON;
GO

PRINT 'size of all tables in the database';
SELECT
	OBJECT_NAME(p.object_id) AS table_name,
	p.data_compression_desc,
    SUM(au.total_pages) AS page_count,
	CAST(SUM(au.total_pages) * 8 / 1024.0 AS decimal(18,2)) AS table_size_mb
FROM sys.partitions p WITH (NOLOCK) 
INNER JOIN sys.allocation_units au WITH (NOLOCK) ON p.hobt_id = au.container_id 
INNER JOIN sys.tables t WITH (NOLOCK) ON p.object_id = t.object_id
WHERE t.is_ms_shipped = 0
GROUP BY p.object_id, p.data_compression_desc
ORDER BY table_size_mb DESC OPTION (RECOMPILE);

PRINT 'key table properties'
SELECT
	t.name AS table_name,
	i.index_id,
	i.name As index_name,
	p.[rows] AS row_count,
	p.data_compression_desc AS 'data_compression',
    t.lock_on_bulk_load, t.is_replicated, t.has_replication_filter, 
    t.is_tracked_by_cdc, t.lock_escalation_desc, t.is_filetable, 
	t.is_memory_optimized, t.durability_desc, 
	t.temporal_type_desc, t.is_remote_data_archive_enabled, t.is_external -- new for SQL Server 2016
FROM sys.tables t WITH (NOLOCK)
INNER JOIN sys.partitions p WITH (NOLOCK) ON t.[object_id] = p.[object_id]
INNER JOIN sys.indexes i WITH (NOLOCK) ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE t.is_ms_shipped = 0
ORDER BY t.name ASC, p.index_id ASC OPTION (RECOMPILE);

PRINT 'size of all indexes in the database';
SELECT
	t.name AS table_name,
	i.name AS index_name,
	i.index_id AS index_id,
	i.fill_factor,
	i.is_disabled,
	i.is_primary_key,
	i.is_unique,
	i.is_unique_constraint,
	p.data_compression_desc,
	SUM(au.used_pages) AS page_count,
	CAST(SUM(au.used_pages) * 8 / 1024.0 AS decimal(18,2)) AS index_size_mb 
FROM sys.indexes i WITH (NOLOCK)
INNER JOIN sys.partitions p WITH (NOLOCK) ON p.object_id = i.object_id AND p.index_id = i.index_id
INNER JOIN sys.allocation_units au WITH (NOLOCK) ON au.container_id = p.partition_id
INNER JOIN sys.tables t WITH (NOLOCK) ON i.object_id = t.object_id
WHERE t.is_ms_shipped = 0
GROUP BY t.name, i.index_id, i.name, i.fill_factor, i.is_disabled, i.is_primary_key, i.is_unique, i.is_unique_constraint, p.data_compression_desc
ORDER BY index_size_mb DESC OPTION (RECOMPILE);

PRINT 'tables and all their triggers';
SELECT
	OBJECT_NAME(parent_id) AS table_name,
    name AS trigger_name,
	create_date,
	modify_date,
	is_ms_shipped,
	is_disabled,
	is_instead_of_trigger
FROM sys.triggers WITH (NOLOCK)
WHERE type = 'TR' OPTION (RECOMPILE);

/*
PRINT 'index and statistics last update date';
SELECT 
	o.object_id,
    sm.name As 'schema_name',
	o.name AS table_name,
	c.name AS column_name,
	s.name AS statistic_name,
    CASE
        WHEN s.auto_created = 0 AND s.user_created = 0 THEN 'index statistics'
        WHEN s.auto_created = 0 AND s.user_created = 1 THEN 'user created statistics'
        WHEN s.auto_created = 1 AND s.user_created = 0 THEN 'auto created statistics'
        WHEN s.auto_created = 1 AND s.user_created = 1 THEN 'unknown'
    END AS statistic_type,
    CASE
        WHEN s.has_filter = 1 THEN 'yes'
        WHEN s.has_filter = 0 THEN 'no'
    END AS has_filter, 
    CASE
		WHEN s.filter_definition IS NULL THEN ''
        WHEN s.filter_definition IS NOT NULL THEN s.filter_definition
    END AS filter_definition,
    CASE
		WHEN sp.modification_counter > 0 AND sp.last_updated < DATEADD(m, -1, GETDATE()) 
			THEN '> 1 month'
		WHEN sp.modification_counter > 0 AND sp.last_updated < DATEADD(wk, -1, GETDATE()) 
			THEN '> 1 week'
		WHEN sp.modification_counter > 0 AND sp.last_updated > DATEADD(wk, -1, GETDATE()) 
			THEN '< 1 week'
		WHEN sp.modification_counter = 0
			THEN 'statistics recent'
		ELSE ''
	END AS warning,
	sp.last_updated,
    sp.rows AS row_count, 
    sp.rows_sampled AS rows_sampled, 
	CAST(((sp.rows_sampled *  1.0 / sp.rows) * 100.0) AS DECIMAL(18, 2)) AS rows_sampled_percent,
	sp.unfiltered_rows AS rows_unfiltered,
    sp.modification_counter AS rows_modified,
	CAST(((sp.modification_counter *  1.0 / sp.rows) * 100.0) AS DECIMAL(18, 2)) AS rows_modified_percent,
	sp.steps AS histogram_steps
FROM sys.stats s WITH (NOLOCK)
INNER JOIN sys.objects o WITH (NOLOCK) ON s.object_id = o.object_id
INNER JOIN sys.schemas sm WITH (NOLOCK) ON o.schema_id = sm.schema_id
INNER JOIN sys.stats_columns sc WITH (NOLOCK) ON sc.[object_id] = s.object_id AND sc.stats_id = s.stats_id
INNER JOIN sys.columns c WITH (NOLOCK) ON o.object_id = c.object_id AND c.column_id = sc.column_id
OUTER APPLY sys.dm_db_stats_properties(o.object_id, s.stats_id) AS sp 
WHERE o.is_ms_shipped = 0 AND o.name <> N'sysdiagrams'
-- AND sp.last_updated < DATEADD(day, -30, GETDATE())
ORDER BY sp.last_updated DESC OPTION (RECOMPILE);
*/

PRINT 'tables (> 1000 pages) with missing clustered index';
SELECT DISTINCT
	t.name AS table_name,
	SUM(au.total_pages) AS page_count,
	SUM(au.total_pages) * 8 AS table_size_kb,
	CAST(SUM(au.total_pages) * 8 / 1024.0 AS DECIMAL(18,2)) AS table_size_mb 
FROM sys.indexes i WITH (NOLOCK)
INNER JOIN sys.partitions p WITH (NOLOCK) ON p.object_id = i.object_id AND p.index_id = i.index_id
INNER JOIN sys.allocation_units au WITH (NOLOCK) ON au.container_id = p.partition_id
INNER JOIN sys.tables t WITH (NOLOCK) ON i.object_id = t.object_id
WHERE i.index_id = 0 AND t.is_ms_shipped = 0
GROUP BY t.name
HAVING SUM(au.total_pages) > 1000
ORDER BY table_size_kb DESC OPTION (RECOMPILE);

PRINT 'clustered indexes with datatypes';
SELECT
	st.name AS table_name,
	si.index_id, si.name AS index_name,
	SUBSTRING((SELECT  ', ' + ac.name + '(' + tp.name + ')'
	FROM sys.tables t WITH (NOLOCK)
	INNER JOIN sys.indexes i WITH (NOLOCK) ON t.object_id = i.object_id
	INNER JOIN sys.index_columns ic WITH (NOLOCK) ON i.object_id = ic.object_id
		AND i.index_id = ic.index_id
	INNER JOIN sys.all_columns ac WITH (NOLOCK) ON t.object_id = ac.object_id
		AND ic.column_id = ac.column_id
	INNER JOIN sys.types tp WITH (NOLOCK) ON ac.system_type_id = tp.system_type_id
	WHERE si.object_id = i.object_id
		AND si.index_id = i.index_id
	ORDER BY ic.key_ordinal
FOR XML PATH('')), 2, 8000) AS columns
FROM sys.indexes si WITH (NOLOCK)
INNER JOIN sys.tables st WITH (NOLOCK) ON st.object_id = si.object_id
WHERE si.index_id = 1
ORDER BY table_name ASC OPTION (RECOMPILE);

PRINT 'index definitions with indexed and included columns in order';
SELECT
	i.index_id,
	i.is_disabled,
	'[' + s.name + '].[' + o.name + ']' AS 'table_name'
    ,+ i.name AS 'index_name'
    ,LOWER(i.type_desc) + CASE 
        WHEN i.is_unique = 1
            THEN ', unique'
        ELSE ''
        END + CASE 
        WHEN i.is_primary_key = 1
            THEN ', primary key'
        ELSE ''
        END AS 'index_description'
    ,STUFF((
            SELECT ', [' + sc.name + ']' AS 'text()'
            FROM syscolumns AS sc WITH (NOLOCK)
            INNER JOIN sys.index_columns AS ic WITH (NOLOCK) ON ic.object_id = sc.id
                AND ic.column_id = sc.colid
            WHERE sc.id = so.object_id
                AND ic.index_id = i1.indid
                AND ic.is_included_column = 0
            ORDER BY key_ordinal
            FOR XML PATH('')
            ), 1, 2, '') AS 'indexed_columns'
    ,STUFF((
            SELECT ', [' + sc.NAME + ']' AS 'text()'
            FROM syscolumns AS sc WITH (NOLOCK)
            INNER JOIN sys.index_columns AS ic WITH (NOLOCK) ON ic.object_id = sc.id
                AND ic.column_id = sc.colid
            WHERE sc.id = so.object_id
                AND ic.index_id = i1.indid
                AND ic.is_included_column = 1
            FOR XML PATH('')
            ), 1, 2, '') AS 'included_columns',
	i.filter_definition
FROM sysindexes AS i1 WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK) ON i.object_id = i1.id
    AND i.index_id = i1.indid
INNER JOIN sysobjects AS o WITH (NOLOCK) ON o.id = i1.id
INNER JOIN sys.objects AS so WITH (NOLOCK) ON so.object_id = o.id
    AND is_ms_shipped = 0
INNER JOIN sys.schemas AS s WITH (NOLOCK) ON s.schema_id = so.schema_id
WHERE so.type = 'U'
    AND i1.indid < 255
    AND i1.status & 64 = 0       --index with duplicates
    AND i1.status & 8388608 = 0  --auto created index
    AND i1.status & 16777216 = 0 --stats no recompute
    AND i.type_desc <> 'heap'
    AND so.name <> 'sysdiagrams'
ORDER BY s.name ASC, o.name ASC, i.index_id ASC OPTION (RECOMPILE);

PRINT 'index usage statistics';
SELECT
	ius.object_id,
	OBJECT_SCHEMA_NAME(ius.object_id) AS 'schema_name',
	OBJECT_NAME(ius.object_id) AS 'object_name',
	i.name AS 'index_name',
	i.fill_factor,
	i.index_id,
	i.is_unique,
	i.is_unique_constraint,
	--i.is_disabled,
	--i.is_hypothetical,
	ius.user_seeks,
	ius.user_scans,
	ius.user_lookups,
	ius.user_updates,
	ius.last_user_seek,
	ius.last_user_scan,
	ius.last_user_lookup,
	ius.last_user_update,
	ius.system_seeks,
	ius.system_scans,
	ius.system_lookups,
	ius.last_system_seek,
	ius.last_system_scan,
	ius.last_user_lookup,
	ius.last_system_update
FROM sys.dm_db_index_usage_stats ius WITH (NOLOCK)
INNER JOIN sys.indexes i WITH (NOLOCK)
	ON i.object_id = ius.object_id
		AND i.index_id = ius.index_id
INNER JOIN sys.tables t WITH (NOLOCK) ON i.object_id = t.object_id
WHERE ius.database_id = DB_ID() AND t.is_ms_shipped = 0
ORDER BY (ius.user_seeks + ius.user_scans + ius.user_lookups) ASC OPTION (RECOMPILE);

PRINT 'index fragmentation statistics';
/*
SELECT 
	ips.object_id AS 'object_id',
	OBJECT_NAME(i.object_id) AS 'object_name',
	ips.partition_number,
	ips.index_id,
	i.name AS 'index_name',
	ips.index_type_desc,
	ips.alloc_unit_type_desc,
	ips.index_depth,
	ROUND(ips.avg_fragmentation_in_percent, 2) AS 'fragmentation_in_percent',
	ips.fragment_count,
	ROUND(ips.avg_fragment_size_in_pages, 2) AS 'avg_fragment_size_in_pages',
	ips.page_count,
	ROUND(ips.avg_page_space_used_in_percent, 2) AS 'page_density',
	ips.record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , 'DETAILED') ips
CROSS APPLY sys.indexes i WITH (NOLOCK)
WHERE i.object_id = ips.object_id
	AND i.index_id = ips.index_id
	AND ips.index_level = 0  -- just the leaf level
	AND ips.alloc_unit_type_desc = 'IN_ROW_DATA' OPTION (RECOMPILE);
*/

PRINT 'disable and drop indexes';
/*
DECLARE @indexname SYSNAME, @schemaname SYSNAME, @tablename SYSNAME;
DECLARE @sqlcmd NVARCHAR(512);

DECLARE index_cursor CURSOR FAST_FORWARD LOCAL FOR  
SELECT
	i.name AS index_name,
	sm.name AS 'schema_name',
	o.name AS table_name,
	i.index_id,
	i.is_unique,
	i.is_unique_constraint,
	i.is_disabled,
	i.is_hypothetical,
	ius.user_seeks,
	ius.user_scans,
	ius.user_lookups,
	ius.user_updates,
	ius.last_user_seek,
	ius.last_user_scan,
	ius.last_user_lookup,
	ius.last_user_update,
	ius.system_seeks,
	ius.system_scans,
	ius.system_lookups,
	ius.last_system_seek,
	ius.last_system_scan,
	ius.last_user_lookup,
	ius.last_system_update
FROM sys.dm_db_index_usage_stats ius WITH (NOLOCK)
INNER JOIN sys.indexes i WITH (NOLOCK) ON i.object_id = ius.object_id
	AND i.index_id = ius.index_id
INNER JOIN sys.objects o WITH (NOLOCK) ON i.object_id = o.object_id
INNER JOIN sys.schemas sm WITH (NOLOCK) ON o.schema_id = sm.schema_id
WHERE o.is_ms_shipped = 0 AND i.index_id > 1
AND i.is_primary_key = 0
AND i.is_unique = 0 AND i.is_unique_constraint = 0
AND ius.user_seeks = 0 AND ius.user_scans = 0 AND ius.user_lookups = 0
-- AND ius.system_seeks = 0 AND ius.system_scans = 0 AND ius.system_lookups = 0
-- AND i.is_disabled = 1
ORDER BY (ius.user_seeks + ius.user_scans + ius.user_lookups) ASC OPTION (RECOMPILE);

OPEN index_cursor  
FETCH NEXT FROM index_cursor INTO @indexname, @schemaname, @tablename;

WHILE @@FETCH_STATUS = 0   
BEGIN   
	SET @sqlcmd = N'ALTER INDEX ' + @indexname + ' ON ' +  @schemaname + '.' + @tablename + ' DISABLE;';
	-- SET @sqlcmd = N'DROP INDEX ' + @indexname + ' ON ' +  @schemaname + '.' + @tablename + ';';
    PRINT @sqlcmd;
	-- EXECUTE (@sqlcmd);
	
    FETCH NEXT FROM index_cursor INTO @indexname, @schemaname, @tablename;   
END

CLOSE index_cursor;
DEALLOCATE index_cursor;
*/

PRINT 'missing index details';
-- look for improvement_measures > 100000 (sqlskills) and avg_user_impact > 80 (microsoft)
SELECT
  CONVERT (DECIMAL (28,1), 
	migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)
  ) AS improvement_measure, 
  mid.statement AS table_name,
  mid.equality_columns,
  mid.inequality_columns,
  mid.included_columns,
  migs.avg_user_impact,
  migs.unique_compiles,
  migs.user_seeks,
  migs.user_scans,
  migs.last_user_seek,
  migs.last_user_scan,
  'CREATE INDEX [missing_index_'
	  + CONVERT (VARCHAR, mig.index_group_handle) + '_'
	  + CONVERT (VARCHAR, mid.index_handle) + '_'
	  + LEFT(PARSENAME(mid.statement, 1), 32) + ']' + ' ON '
	  + mid.statement
	  + ' (' + ISNULL(mid.equality_columns, '')
	  + CASE WHEN mid.equality_columns IS NOT NULL
	  AND mid.inequality_columns IS NOT NULL THEN ','
	  ELSE ''
	  END + ISNULL(mid.inequality_columns, '') + ')'
	  + ISNULL(' INCLUDE (' + mid.included_columns + ')', '')
  AS create_index_statement
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
--AND migs.avg_total_user_cost * ( migs.avg_user_impact / 100.0 )
--	* ( migs.user_seeks + migs.user_scans ) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC OPTION (RECOMPILE);

-- find missing index warnings for cached plans in the current database (Missing Index Warnings)
-- note: This query could take some time on a busy instance
SELECT TOP(25)
	OBJECT_NAME(objectid) AS 'object_name', 
    cp.objtype AS 'object_type',
	cp.usecounts,
	cp.size_in_bytes,
	query_plan
FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
WHERE CAST(query_plan AS NVARCHAR(MAX)) LIKE N'%MissingIndex%'
AND dbid = DB_ID()
ORDER BY cp.usecounts DESC OPTION (RECOMPILE);